import pymysql


# 连接初始化 获取连接对象
def init(ip, port, db, user, password):
    return pymysql.connect(host=ip, port=port, db=db, user=user, password=password, charset="utf8")


# 获取cursor对象
def getCursor(obj):
    return obj.cursor()


# 提交事务并且关闭数据库对象流和连接流
def submit(db, cursor):
    db.commit()
    cursor.close()
    db.close()

# 判断输入的编号功能是否正确
def numError(curse, cursename):
    if curse < 0 or curse > 100:
        print("{} 无效 请重新输入:".format(cursename))
        curse = float(input())


# 查询所有学生信息
def searchALL():
    # 初始化
    db = init("127.0.0.1", 3306, "python", "root", "123456")
    cursor = getCursor(db)
    # 定义sql并执行
    sql = "select * from student"
    cursor.execute(sql)
    # 获取sql的结果集
    result = cursor.fetchall()

    datalength = len(result)
    # 获取查询的结果 存到一个元组中
    # 判断查询的数据长度是否为空
    if datalength == 0:
        print("当前没有学生!")
    else:
        print("id name chinese math english total")
        for i in result:
            id = i[0]
            name = i[1]
            chinese = i[2]
            math = i[3]
            english = i[4]
            total = i[5]
            print("{} {} {} {} {}".format(id, name, chinese, math, english, total))
    submit(db, cursor)


# 查询指定id学生信息
def searchID(id):
    db = init("127.0.0.1", 3306, "python", "root", "123456")
    cursor = getCursor(db)
    sql = "select * from student where stu_id = %s" % id
    cursor.execute(sql)
    result = cursor.fetchall()
    datalength = len(result)
    if datalength == 0:
        print("id为 {} 的学生不存在! 请重新输入id:".format(id), end="")
        searchID(int(input()))
    for i in result:
        id = i[0]
        name = i[1]
        chinses = i[2]
        math = i[3]
        english = i[4]
        total = i[5]
        print("id = {} name = {} chinses = {} math = {} english = {} total = {}"
              .format(id, name, chinses, math, english, total))
    submit(db, cursor)


# 添加新学生
def insert(name, chinese, math, english):
    db = init("127.0.0.1", 3306, "python", "root", "123456")
    cursor = getCursor(db)
    total = chinese + math + english
    sql = "insert into student(name,chinese,math,english,total) values('%s', %s, %s, %s, %s)" % (
        name, chinese, math, english, total)
    cursor.execute(sql)
    submit(db, cursor)


# 更新学生数据
def update(name, field, value):
    db = init("127.0.0.1", 3306, "python", "root", "123456")
    cursor = getCursor(db)
    if field == "name":
        sql = "update student set %s = '%s' where name = '%s'" % (field, value, name)
    else:
        sql = "update student set %s = %s where name = '%s'" % (field, value, name)

    cursor.execute(sql)
    submit(db, cursor)


# 删除学生
def delete(name):
    db = init("127.0.0.1", 3306, "python", "root", "123456")
    cursor = getCursor(db)
    sql = "delete from student where name = '%s'" % name
    cursor.execute(sql)
    submit(db, cursor)


# 操作终端
def operation(num):
    if num == 1:
        searchALL()
    elif num == 2:
        print("请输入指定id:", end="")
        searchID(int(input()))
    elif num == 3:
        print("请输入学生姓名:", end="")
        name = input()
        print("请输入语文成绩:", end="")
        chinese = float(input())
        print("请输入数学成绩:", end="")
        math = float(input())
        print("请输入英语成绩:", end="")
        english = float(input())
        numError(chinese, "语文")
        numError(math, "数学")
        numError(english, "英语")
        insert(name, chinese, math, english)
    elif num == 4:
        print("请输入想修改学生的姓名和数据")
        print("name:", end="")
        name = input()
        print("filed:", end="")
        filed = input()
        print("value:", end="")
        if "name" == filed:
            value = input()
        else:
            value = int(input())
        update(name, filed, value)
    else:
        print("请输入想删除学生的名字:", end="")
        name = input()
        delete(name)


# 创建菜单界面
def menu():
    while True:
        print("输入1 查询所有学生信息")
        print("输入2 查询指定id学生信息")
        print("输入3 添加新学生")
        print("输入4 更新学生数据")
        print("输入5 删除学生")
        print("输入6 退出系统")
        print("请输入:", end="")
        num = int(input())
        if num == 6:
            break
        operation(num)


print("欢迎使用学生管理系统!!!")
menu()
